package com.codeoss.framework.db.table;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.beans.BeansException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.transaction.annotation.Transactional;

import com.codeoss.framework.context.YunContext;
import com.codeoss.framework.data.metadata.Column;
import com.codeoss.framework.data.metadata.Expression;
import com.codeoss.framework.data.metadata.Order;
import com.codeoss.framework.data.metadata.Query;
import com.codeoss.framework.data.metadata.Table;
import com.codeoss.framework.data.metadata.TableData;
import com.codeoss.framework.db.column.IColumnService;
import com.codeoss.framework.util.DBUtil;
import com.codeoss.framework.util.StringUtils;

public class AbstractTableService implements ITableService, ApplicationContextAware {
	@Autowired
	protected JdbcTemplate jdbcTemplate;
	protected ApplicationContext applicationContext;
	@Autowired
	private DBUtil dbUtil;
	@Override
	@Transactional
	public Object insert(YunContext yunContext, Table table, Map<String, Object> dataMap) {
		// TODO Auto-generated method stub
		
		dataMap.remove(table.getPrimaryKeyColumn().getColumnName());
		
		List<String> insertColumnList = new ArrayList<String>(dataMap.keySet());
		final String sql = getInsertSQL(table, insertColumnList);
		KeyHolder keyHolder = new GeneratedKeyHolder();
		
		jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				// TODO Auto-generated method stub
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

				for (int i = 0; i < insertColumnList.size(); i++) {
					String columnName = insertColumnList.get(i);
					Column column = table.getColumnByColumnName(columnName);
					Object object = dataMap.get(columnName);
					IColumnService columnService = (IColumnService) applicationContext
							.getBean(column.getDataType() + "ColumnService");
					columnService.setValue(yunContext, column, ps, i + 1, object);
				}

				return ps;
			}

		}, keyHolder);
		return keyHolder.getKeys().get(table.getPrimaryKeyColumn().getColumnName());
	}

	@Override
	@Transactional
	public int update(YunContext yunContext, Table table, Map<String, Object> dataMap, Query query) {
		// TODO Auto-generated method stub
		List<String> updateColumnList = new ArrayList<String>(dataMap.keySet());
		String updateSQL = getUpdateSQL(table, updateColumnList, query);
		return jdbcTemplate.update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				// TODO Auto-generated method stub
				PreparedStatement ps = conn.prepareStatement(updateSQL);
				int index=1;
				for (int i = 0; i < updateColumnList.size(); i++) {
					String columnName = updateColumnList.get(i);
					Column column = table.getColumnByColumnName(columnName);
					if (column.isPrimaryKey())
						continue;
					Object object = dataMap.get(columnName);
					IColumnService columnService = (IColumnService) applicationContext
							.getBean(column.getDataType() + "ColumnService");
					columnService.setValue(yunContext, column, ps, index, object);
					index++;
				}
				fillWhere(table, query, ps, index);
				return ps;
			}
		});
	}

	@Override
	@Transactional
	public int delete(YunContext yunContext, Table table, Query query) {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append("DELETE FROM ").append(table.getTableName());
		String where = getWhere(table, query,false);
		sb.append(where);
		String deleteSQL = sb.toString();
		return jdbcTemplate.update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				// TODO Auto-generated method stub
				PreparedStatement ps = conn.prepareStatement(deleteSQL);
				fillWhere(table, query, ps, 1);
				return ps;
			}
		});
	}

	@Override
	public TableData select(YunContext yunContext, Table table, List<String> columnList, Query query) {
		// TODO Auto-generated method stub
		TableData tableData = new TableData();

		StringBuilder sb = new StringBuilder();
		sb.append("select count(1) FROM ").append(table.getTableName());
		if(query!=null) {
			String where = getWhere(table, query,true);
			sb.append(where);
		}
		String countSQL = sb.toString();
		int count = jdbcTemplate.execute(countSQL, new PreparedStatementCallback<Integer>() {
			@Override
			public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				// TODO Auto-generated method stub
				fillWhere(table, query, ps, 1);
				ResultSet rs = ps.executeQuery();
				if (rs.next()) {
					try {
						return rs.getInt(1);
					} finally {
						dbUtil.closeResultSet(rs);
					}
				}
				return null;
			}
		});

		if (count > 0) {
			tableData.setTotal(count);
			if (query != null && query.isPaginationQuery()) {
				List<String> selectList = getColumnList(table, null);
				String pageSql=getPageSQL(yunContext, table, selectList, query);
				List<Map<String, Object>> mapList = jdbcTemplate.execute(pageSql,
						new PreparedStatementCallback<List<Map<String, Object>>>() {
							@Override
							public List<Map<String, Object>> doInPreparedStatement(PreparedStatement ps)
									throws SQLException, DataAccessException {
								// TODO Auto-generated method stub
								fillWhere(table, query, ps, 1);
								ResultSet rs = ps.executeQuery();
								List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
								try {
									while (rs.next()) {
										Map<String, Object> map=new HashMap<String, Object>();
										dataList.add(map);
										for (int i = 0; i < selectList.size(); i++) {
											String columnName = selectList.get(i);
											Column column = table.getColumnByColumnName(columnName);
											IColumnService columnService = (IColumnService) applicationContext
													.getBean(column.getDataType() + "ColumnService");
											Object value=columnService.getValue(yunContext, column, rs);
											map.put(columnName, value);
										}
									}
								} finally {
									dbUtil.closeResultSet(rs);
								}
								return dataList;
							}
						});
				tableData.setData(mapList);
			} else {
				List<String> selectList = getColumnList(table, null);
				String selectSQL = getSelectSQL(yunContext, table, selectList, query);

				List<Map<String, Object>> mapList = jdbcTemplate.execute(selectSQL,
						new PreparedStatementCallback<List<Map<String, Object>>>() {
							@Override
							public List<Map<String, Object>> doInPreparedStatement(PreparedStatement ps)
									throws SQLException, DataAccessException {
								// TODO Auto-generated method stub
								fillWhere(table, query, ps, 1);
								ResultSet rs = ps.executeQuery();
								List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
								try {
									while (rs.next()) {
										Map<String, Object> map=new HashMap<String, Object>();
										dataList.add(map);
										for (int i = 0; i < selectList.size(); i++) {
											String columnName = selectList.get(i);
											Column column = table.getColumnByColumnName(columnName);
											IColumnService columnService = (IColumnService) applicationContext
													.getBean(column.getDataType() + "ColumnService");
											Object value=columnService.getValue(yunContext, column, rs);
											map.put(columnName, value);
										}
									}
								} finally {
									dbUtil.closeResultSet(rs);
								}
								return dataList;
							}
						});
				tableData.setData(mapList);
			}
		}

		return tableData;
	}

	protected String getInsertSQL(Table table, List<String> insertColumnList) {
		StringBuilder sb = new StringBuilder();
		sb.append("insert into ");
		sb.append(table.getTableName());
		sb.append("(");
		int index = 0;
		for (int i = 0; i < insertColumnList.size(); i++) {
			if (index > 0) {
				sb.append(",");
			}
			sb.append(insertColumnList.get(i));
			index++;
		}
		sb.append(")");

		sb.append(" values(");

		index = 0;
		for (int i = 0; i < insertColumnList.size(); i++) {
			if (index > 0) {
				sb.append(",");
			}
			sb.append("?");
			index++;
		}
		sb.append(")");
		return sb.toString();
	}

	protected String getUpdateSQL(Table table, List<String> updateColumnList, Query query) {
		StringBuilder sb = new StringBuilder();
		sb.append("UPDATE ").append(table.getTableName());
		int index = 0;
		for (int i = 0; i < updateColumnList.size(); i++) {
			String columnName = updateColumnList.get(i);
			Column column = table.getColumnByColumnName(columnName);
			if (column.isPrimaryKey())
				continue;
			if (index > 0) {
				sb.append(",");
			} else {
				sb.append(" SET ");
			}
			sb.append(updateColumnList.get(i)).append("=");
			sb.append("?");
			index++;
		}

		String where = getWhere(table, query,false);
		sb.append(where);

		return sb.toString();
	}
	protected String getWhere(Table table, Query query,boolean isCountQuery) {
		return getWhere(table,query,isCountQuery,true);
	}
	
	protected String getWhere(Table table, Query query,boolean isCountQuery,boolean isContainOrderBy) {
		StringBuilder sb = new StringBuilder();
		List<Expression> expressionList = query.getExpressionList();
		if (expressionList.size() > 0) {
			sb.append(" WHERE 1=1 ");
			for (int i = 0; i < expressionList.size(); i++) {
				Expression expression = expressionList.get(i);
				String operation = expression.getOperation();
				if ("and".equals(operation)) {
					sb.append(" AND ");
					sb.append(expression.getName());
					sb.append("=?");
				} else if ("or".equals(operation)) {
					sb.append(" OR ");
					sb.append(expression.getName());
					sb.append("=?");
				} else if ("notnull".equals(operation)) {
					sb.append(expression.getName());
					sb.append(" is not null");
				} else if ("=".equals(operation) || "<".equals(operation) || ">".equals(operation)
						|| "<=".equals(operation) || ">=".equals(operation) || "like".equals(operation)
						|| "<>".equals(operation)) {
					sb.append(" AND ");
					sb.append(expression.getName());
					if("like".equals(operation)){
						sb.append(" ");
					}
					sb.append(operation);
					sb.append(" ? ");
				} else if ("in".equals(operation)) {
					sb.append(" AND ");
					sb.append(expression.getName());
					sb.append(" in (");
					if(expression.getValue1() instanceof Collection) {
						Collection<Object> c=(Collection<Object>)expression.getValue1();
						Iterator<Object> it=c.iterator();
						int index=0;
						while(it.hasNext()) {
							if(index>0) {
								sb.append(",");
							}
							index++;
							sb.append(it.next());
						}
					}else {
						Object object[] = (Object[]) expression.getValue1();
						for (int j = 0; j < object.length; j++) {
							if (j != 0) {
								sb.append(",");
							}
							sb.append(object[j]);
						}
					}
					sb.append(")");
				} else if ("between".equals(operation)) {
					sb.append(" AND ");
					sb.append(expression.getName());

					sb.append(" BETWEEN ");

					sb.append("?");

					sb.append(" AND ");

					sb.append("?");

					sb.append(")");
				} else {
					String sql = expression.getSQL();
					if (!StringUtils.isEmpty(sql)) {
						sb.append(" AND ");
						sb.append("(");
						sb.append(sql);
						sb.append(")");
					}
				}
			}
		}
		if(isContainOrderBy) {
			List<Order> orderList= query.getOrderList();
			if(!isCountQuery && orderList.size()>0) {
				sb.append(" order by ");
				for (int i = 0; i < orderList.size(); i++) {
					Order order=orderList.get(i);
					
					if(i>0){
						sb.append(",");
	    			}
	    			String ordersql=order.getOrderSQL();
	    			if(!StringUtils.isEmpty(ordersql)){
	    				sb.append(ordersql);
	    			}else{
	    				sb.append(order.getName()).append(" ").append(order.getOrder());
	    			}
				}
			}
		}
		return sb.toString();
	}

	protected void fillWhere(Table table, Query query, PreparedStatement ps, int startIndex) throws SQLException {
		if(query==null) {
			return;
		}
		List<Expression> expressionList = query.getExpressionList();
		if (expressionList.size() > 0) {
			for (int i = 0; i < expressionList.size(); i++) {
				Expression expression = expressionList.get(i);
				String operation = expression.getOperation();
				if ("and".equals(operation)) {
					ps.setObject(startIndex, expression.getValue1());
					startIndex++;
				} else if ("or".equals(operation)) {
					ps.setObject(startIndex, expression.getValue1());
					startIndex++;
				} else if ("notnull".equals(operation)) {

				} else if ("=".equals(operation) || "<".equals(operation) || ">".equals(operation)
						|| "<=".equals(operation) || ">=".equals(operation) || "like".equals(operation)
						|| "<>".equals(operation)) {
					ps.setObject(startIndex, expression.getValue1());
					startIndex++;
				} else if ("in".equals(operation)) {

				} else if ("between".equals(operation)) {
					ps.setObject(startIndex, expression.getValue1());
					startIndex++;
					ps.setObject(startIndex, expression.getValue2());
					startIndex++;
				}
			}
		}
	}

	protected List<String> getColumnList(Table table, List<String> columns) {
		List<String> columnList = new ArrayList<String>();
		if (columns == null) {
			List<Column> allColumnList = table.getAllColumnList();
			for (int i = 0; i < allColumnList.size(); i++) {
				columnList.add(allColumnList.get(i).getColumnName());
			}
		} else {
			for (int i = 0; i < columns.size(); i++) {
				columnList.add(columns.get(i));
			}
		}
		return columnList;
	}

	public String getPageSQL(YunContext yunContext, Table table, List<String> columnList, Query query) {
		return "";
	}

	protected String getSelectSQL(YunContext yunContext, Table table, List<String> selectColumnList, Query query) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		for (int i = 0; i < selectColumnList.size(); i++) {
			if (i > 0) {
				sb.append(",");
			}
			sb.append(selectColumnList.get(i));
		}
		sb.append(" FROM ").append(table.getTableName());
		if(query!=null) {
			String where = getWhere(table, query,false);
			sb.append(where);
		}
		return sb.toString();
	}

	@Override
	public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
		// TODO Auto-generated method stub
		this.applicationContext = applicationContext;
	}
}
